import pymysql

# 连接mysql
connection = pymysql.connect(
    host='localhost',       # 本地主机
    port=3306,              # 默认端口
    user='root',            # 用户名
    password='12345678',      # 密码
    database="player_data"        # 数据库名称
    )
# 在result_list新建用户分数，并设为0
def add_to_result_list(id, username):
    # 創建游標對象
    cursor = connection.cursor()
    # 执行插入语句
    query = "INSERT INTO result_list (ID, Name, Result) VALUES (%s, %s, %s)"
    values = (id, username, 0)
    cursor.execute(query, values)
    connection.commit()
    # 關閉游標對象
    cursor.close()


# 新建用户到数据库
def add_to_users_list(name, password):
    # 創建游標對象
    cursor = connection.cursor()
    # 执行插入语句
    query = "INSERT INTO users_list (Name, Password) VALUES (%s, %s)"
    values = (name, password)
    cursor.execute(query, values)
    # 获取用户ID
    user_id = cursor.lastrowid
    connection.commit()
    # 關閉游標對象
    cursor.close()
    # 在result_list建立用户数据
    add_to_result_list(user_id, name)
    # 返回用户ID
    return user_id


# 通过输入用户名称及密码取得ID
def get_id_by_name_password(username, password):
    # 創建游標對象
    cursor = connection.cursor()
    # 执行选择语句
    query = "SELECT ID FROM users_list WHERE Name = %s AND Password = %s"
    value = (username, password)
    cursor.execute(query, value)
    # 提取结果
    result = cursor.fetchone()
    # 關閉游標對象
    cursor.close()
    # 若ID存在则返回ID，否则返回None
    return result[0] if result else None


# 检查输入名称是否存在于数据库
def check_name_existence(username):
    # 創建游標對象
    cursor = connection.cursor()
    # 执行检查语句
    query = "SELECT EXISTS(SELECT 1 FROM users_list WHERE Name = %s)"
    value = (username,)
    cursor.execute(query, value)
    # 提取结果
    result = cursor.fetchone()[0]
    # 關閉游標對象
    cursor.close()
    # 返回檢查結果 True or False
    return result


# 登入
def login(username, password):
    # 取用户ID
    userid = get_id_by_name_password(username, password)
    # 检查用户资料正确返回用户ID，否则返回错误
    if userid is not None:
        return userid
    else:
        return "!username or password wrong!"


# 注册
def signup(username, password):
    # 对新名称作重复检查
    name_exists = check_name_existence(username)
    # 如果名称已存在返回错误，否则返回新建用户的ID
    if name_exists:
        return "!this username already exists!"
    else:
        return add_to_users_list(username, password)



# if __name__ == '__main__':
# print(signup("Tom", "ecnuhao"))
# print(login("Tom", "ecnuhao"))
# connection.close()

def start_to_play():
    while True:
        are_you_new_here = input("Sir, are you new here?(yes/no): ")
        print()
        if are_you_new_here == "yes":
            signup_status = '!this username already exists!'
            while(signup_status == '!this username already exists!'):
                playername = input("New boy, what your name:")
                print()
                playerpassword = input("Set your password please:")
                print()
                signup_status = signup(playername,playerpassword )
                print(signup_status)
            print("Haha!welcome to my world!")
            connection.close()
            res = [signup_status, playername,playerpassword]
            return res
        elif are_you_new_here == "no":
            login_status = '!username or password wrong!'
            while(login_status == '!username or password wrong!'):
                playername = input("Master, what your name:")
                print()
                playerpassword = input("Let me check your password please:")
                print()
                login_status = login(playername,playerpassword)
                print(login_status)
            print('welcome master,enjoy yourself!!')
            connection.close()
            res = [login_status, playername, playerpassword]
            return res
        else:
            print('God! you fool!!')
            print('what the fuck you are saying!')
            print('Answer me yes or no!!!!')
            print()
